﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using DALayer;

namespace BALayer
{
    public class DBDoanVien
    {
        DAL db=null;
        public DBDoanVien()
        {
            db = new DAL();
        }
        public DataSet LayDoanVien()
        {
            return db.ExecuteQueryDataSet("select * from DoanVien", CommandType.Text, null);
        }
        public bool ThemDoanVien(ref string err, string MaDV, string Ten, 
            bool Nam, DateTime NgaySinh, string NoiSinh, 
            string Mail, string MaCV, string MaSD, string MaLop)
        {
            return db.MyExecuteNonQuery("ThemDoanVien",
                CommandType.StoredProcedure, ref err,
                new SqlParameter("@MaDV", MaDV),
                new SqlParameter("@Ten", Ten),
                new SqlParameter("@Nam", Nam),
                new SqlParameter("@NgaySinh", NgaySinh),
                new SqlParameter("@NoiSinh",NoiSinh),
                new SqlParameter("@Mail",Mail),
                new SqlParameter("@MaCV",MaCV),
                new SqlParameter("@MaSD", MaSD),
                new SqlParameter("@MaLop", MaLop));
        }
        public bool SuaDoanVien(ref string err, string MaDV, string Ten,
            bool Nam, DateTime NgaySinh, string NoiSinh,
            string Mail, string MaCV, string MaSD, string MaLop)
        {
            return db.MyExecuteNonQuery("SuaDoanVien",
                CommandType.StoredProcedure, ref err,
                new SqlParameter("@MaDV", MaDV),
                new SqlParameter("@Ten", Ten),
                new SqlParameter("@Nam", Nam),
                new SqlParameter("@NgaySinh", NgaySinh),
                new SqlParameter("@NoiSinh", NoiSinh),
                new SqlParameter("@Mail", Mail),
                new SqlParameter("@MaCV", MaCV),
                new SqlParameter("@MaSD", MaSD),
                new SqlParameter("@MaLop", MaLop));
        }
        public bool XoaDoanVien(ref string err, string MaDV)
        {
            return db.MyExecuteNonQuery("XoaDoanVien",
                CommandType.StoredProcedure, ref err,
                new SqlParameter("@MaDV", MaDV));
        }

        public DataSet LayCanBo()
        {
            return db.ExecuteQueryDataSet("select * from DoanVien d where d.MaCV is not null", CommandType.Text, null);
        }

        public DataSet LayCanBoLienHe()
        {
            return db.ExecuteQueryDataSet("select MaDV, Ten, Mail, MaCV from DoanVien where MaCV is not null", CommandType.Text, null);
        }

        public DataSet LayChucVu()
        {
            return db.ExecuteQueryDataSet("select * from ChucVu", CommandType.Text, null);
        }

        public DataSet LayMatKhau(string TenDN)
        {
            return db.ExecuteQueryDataSet("select Password from Account where Username='" + TenDN + "'", CommandType.Text, null);
        }

        public DataSet LayBCH(string ma)
        {
            return db.ExecuteQueryDataSet("select Ten from DoanVien where MaDV='" + ma + "'", CommandType.Text, null);
        }

        public bool ChinhSuaMK(ref string err, string TenDN, string MK)
        {
            return db.MyExecuteNonQuery("ChinhSuaMK",
                CommandType.StoredProcedure, ref err,
                new SqlParameter("@Username", TenDN),
                new SqlParameter("@Password", MK));
        }
    }
}
